﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.EntitesDAL
{
    public class BangdiemDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Implement

        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            return ExecuteQuery("sp_SelectAll_Bangdiem");
        }

        public DataTable Select(string id)
        {
            string sql = @"Select a.MaHV,a.Hoten,(Case when Gioitinh=0 then 'Nam' else N'Nữ' end) as Sex,a.Namsinh, b.DiemLT,b.DiemTH,b.Xeploai,b.Ghichu
                           From tb_Hocvien a join tb_Bangdiem b on a.MaHV=b.MaHVID
                           Where  a.MalopID = '{0}' ";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }

        public DataTable Select_Bangdiem_Theokhoa(string _makhoa)
        {
            try
            {
                string sql = @" Select a.MaHV,a.Hoten,(Case when Gioitinh=0 then 'Nam' else N'Nữ' end) as Sex,a.Namsinh,a.Dienthoai,a.Diachi, b.DiemLT,b.DiemTH,b.Xeploai,b.Ghichu,d.Tenkhoa
                                From tb_Hocvien a join tb_Bangdiem b on a.MaHV=b.MaHVID
                                                  join tb_Lophoc c on a.MalopID = c.Malop
                                                  join tb_Khoahoc d on c.MakhoaID=d.Makhoa
                                Where  c.MakhoaID = '{0}' ";
                sql = string.Format(sql, _makhoa);
                return ExecuteQuery(sql);
            }
            catch { return null; }
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            var sql = "sp_Delete_Bangdiem {0}";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Bangdiem)obj;
            var sql = "set dateformat dmy; execute sp_Insert_Bangdiem '{0}', '{1}', '{2}', N'{3}', N'{4}' ";
            sql = string.Format(sql, o.MaHVID, o.DiemLT, o.DiemTH, o.Xeploai, o.Ghichu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Bangdiem)obj;
            var sql = "set dateformat dmy; execute sp_Update_Bangdiem '{0}', '{1}', '{2}', '{3}', N'{4}', N'{5}' ";
            sql = string.Format(sql, o.Id, o.MaHVID, o.DiemLT, o.DiemTH, o.Xeploai, o.Ghichu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

      
        #endregion


        #region other 

        public DataTable Select_Xemdiem(string _id)
        {
            string sql = @"Select a.MaHV,a.Hoten,(Case when Gioitinh=0 then 'Nam' else N'Nữ' end) as Sex,a.Namsinh,a.Diachi, b.DiemLT,b.DiemTH,b.Xeploai,b.Ghichu
                           From tb_Hocvien a join tb_Bangdiem b on a.MaHV=b.MaHVID
                                             join tb_Lophoc c on a.MalopID=c.Malop
                           Where c.MakhoaID = '{0}'  ";
            sql = string.Format(sql, _id);
            return ExecuteQuery(sql);
        }

        public DataTable Select_Xemdiem(string _id1, string _id2)
        {
            string sql = @"Select a.Hoten,(Case when Gioitinh=0 then 'Nam' else N'Nữ' end) as Sex,a.Namsinh,a.Diachi, b.DiemLT,b.DiemTH,b.Xeploai,b.Ghichu,b.Id,b.MaHVID
                           From tb_Hocvien a join tb_Bangdiem b on a.MaHV=b.MaHVID
                                             join tb_Lophoc c on a.MalopID=c.Malop
                           Where c.MakhoaID = '{0}' and a.MalopID = '{1}'  ";
            sql = string.Format(sql, _id1,_id2);
            return ExecuteQuery(sql);
        }

        #endregion
    }
}
